<?php
require_once dirname(__FILE__) . '/../includes/common.inc.php';

header('Content-Type: text/plain');

$tablePayment = 'pz_payment';
$tableExpense = 'pz_expense';
$tableUser = 'pz_user';
$tableTransfer = 'pz_transfer';
try {
  $hostname = HOST_NAME;
  $username = USER_NAME;
  $password = PASSWORD;
  $dbname = DATABASE_NAME;
  $myPdo = new PDO ('mysql:host=' . $hostname, $username, $password);
  $myPdo->exec('USE ' . $dbname);
  $myPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {
  echo $e->getMessage ();
}

$action = MiscUtils::getParam('action', NULL);

try {
  switch ($action) {
    case 'ietransfer':
      ietransfer($myPdo);
      break;
    case 'ietransfers':
      ietransfers($myPdo);
      break;
    case 'iepayment':
      iepayment($myPdo);
      break;
    case 'iepayments':
      iepayments($myPdo);
      break;
    case 'ieexpense':
      ieexpense($myPdo);
      break;
    case 'ieexpenses':
      ieexpenses($myPdo);
      break;
    case 'iesumall':
      iesumall($myPdo);
      break;
    case 'iesumtransferin':
      iesumtransferin($myPdo);
      break;
    case 'iesumtransfer':
      iesumtransfer($myPdo);
      break;
    default:
      break;
  }
} catch (PDOException $e) {
  echo $e->getMessage();
}

function _months($from, $to) {
  $result = array();
  for ($i = $from->year, $il = $to->year; $i <= $il; $i++) {
    for ($j = 1, $jl = 12; $j <= $jl; $j++) {
      if ($i == $from->year && $j < $from->month) {
        continue;
      } else if ($i == $to->year && $j > $to->month) {
        continue;
      }
      $tmp = new stdClass();
      $tmp->month = $j;
      $tmp->year = $i;
      $result[] = $tmp;
    }
  }
  return $result;
}

function _days($year, $month) {
  if ($month == 1) {
    return 31;
  } else if ($month == 2) {
    if ($year % 4 == 0) {
      return 29;
    } else {
      return 28;
    }
  } else if ($month == 3) {
    return 31;
  } else if ($month == 4) {
    return 30;
  } else if ($month == 5) {
    return 31;
  } else if ($month == 6) {
    return 30;
  } else if ($month == 7) {
    return 31;
  } else if ($month == 8) {
    return 31;
  } else if ($month == 9) {
    return 30;
  } else if ($month == 10) {
    return 31;
  } else if ($month == 11) {
    return 30;
  } else if ($month == 12) {
    return 31;
  }
}


function ietransfer($myPdo) {
  global $tableTransfer;
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $res = new stdClass();
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tableTransfer AS d WHERE $condition AND d.status != -1";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->transfer = ($tmp->result) ? $tmp->result : 0;
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tableTransfer AS d WHERE $condition AND d.status != -1 AND d.method = 'C'";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->cash = ($tmp->result) ? $tmp->result : 0;
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tableTransfer AS d WHERE $condition AND d.status != -1 AND d.method != 'C'";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->noncash = ($tmp->result) ? $tmp->result : 0;
  }
  echo json_encode($res);
}

function ietransfers($myPdo) {
  global $tableTransfer;
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $pageSkip = ($page - 1) * $size;

  $result = new stdClass();
  $result->data = array();
  $result->page = $page;
  $result->size = $size;
  $result->order = $order;
  $result->queue = $queue;
  $result->condition = $condition;

  $sql = "SELECT * FROM $tableTransfer AS d WHERE $condition";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  $result->total = $stmt->rowCount();
  
  $sql = "SELECT DISTINCT d.id, d.* FROM $tableTransfer AS d WHERE $condition ORDER BY $order $queue LIMIT $pageSkip, $size";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  $i = 0;
  while ($i < $stmt->rowCount()) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $tmp->createdDate = ($tmp->createdDate != 0) ? SimpleDate::fromStamp($tmp->createdDate) : 0;
    $tmp->transferDate = ($tmp->transferDate != 0) ? SimpleDate::fromStamp($tmp->transferDate) : 0;
    $tmp->cancelledDate = ($tmp->cancelledDate != 0) ? SimpleDate::fromStamp($tmp->cancelledDate) : 0;
    $result->data[] = $tmp;
    $i++;
  }
  echo json_encode($result);
}

function iepayment($myPdo) {
  global $tablePayment;
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $res = new stdClass();
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tablePayment AS d WHERE $condition AND d.status != -1";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->payment = ($tmp->result) ? $tmp->result : 0;
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tablePayment AS d WHERE $condition AND d.status != -1 AND d.method = 'C'";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->cash = ($tmp->result) ? $tmp->result : 0;
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tablePayment AS d WHERE $condition AND d.status != -1 AND d.method != 'C'";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->noncash = ($tmp->result) ? $tmp->result : 0;
  }
  echo json_encode($res);
}

function iepayments($myPdo) {
  global $tablePayment;
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', 'WHERE 1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $pageSkip = ($page - 1) * $size;

  $result = new stdClass();
  $result->data = array();
  $result->page = $page;
  $result->size = $size;
  $result->order = $order;
  $result->queue = $queue;
  $result->condition = $condition;

  $sql = "SELECT * FROM $tablePayment AS d WHERE $condition";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  $result->total = $stmt->rowCount();
  
  $sql = "SELECT DISTINCT d.id, d.* FROM $tablePayment AS d WHERE $condition ORDER BY $order $queue LIMIT $pageSkip, $size";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  $i = 0;
  while ($i < $stmt->rowCount()) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $tmp->createdDate = ($tmp->createdDate != 0) ? SimpleDate::fromStamp($tmp->createdDate) : 0;
    $tmp->payDate = ($tmp->payDate != 0) ? SimpleDate::fromStamp($tmp->payDate) : 0;
    $tmp->cancelledDate = ($tmp->cancelledDate != 0) ? SimpleDate::fromStamp($tmp->cancelledDate) : 0;
    $result->data[] = $tmp;
    $i++;
  }
  echo json_encode($result);
}


function ieexpense($myPdo) {
  global $tableExpense;
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $res = new stdClass();
  
  $user = json_decode(MiscUtils::getParam('u', NULL));

  if ($user) {
    if ($user->type == USER_TYPE_STAFF) {
      $condition = $condition . " AND (d.userTrackId = '$user->trackId') ";
    } else {
      $condition = $condition;
    }
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tableExpense AS d WHERE $condition AND d.status != -1";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->expense = ($tmp->result) ? $tmp->result : 0;
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tableExpense AS d WHERE $condition AND d.status != -1 AND d.method = 'C'";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->cash = ($tmp->result) ? $tmp->result : 0;
  }
  
  $sql = "SELECT SUM(d.amount) AS result FROM $tableExpense AS d WHERE $condition AND d.status != -1 AND d.method != 'C'";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  if ($stmt->rowCount() == 1) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $res->noncash = ($tmp->result) ? $tmp->result : 0;
  }
  echo json_encode($res);
}

function ieexpenses($myPdo) {
  global $tableExpense;
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', 'WHERE 1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $pageSkip = ($page - 1) * $size;

  $result = new stdClass();
  $result->data = array();
  $result->page = $page;
  $result->size = $size;
  $result->order = $order;
  $result->queue = $queue;
  $result->condition = $condition;
  
  $user = json_decode(MiscUtils::getParam('u', NULL));
  
  if ($user) {
    if ($user->type == USER_TYPE_STAFF) {
      $condition = $condition . " AND (d.userTrackId = '$user->trackId') ";
    } else {
      $condition = $condition;
    }
  }
  
  $sql = "SELECT * FROM $tableExpense AS d WHERE $condition";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  $result->total = $stmt->rowCount();
  
  $sql = "SELECT DISTINCT d.id, d.* FROM $tableExpense AS d WHERE $condition ORDER BY $order $queue LIMIT $pageSkip, $size";
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  $i = 0;
  while ($i < $stmt->rowCount()) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $tmp->createdDate = ($tmp->createdDate != 0) ? SimpleDate::fromStamp($tmp->createdDate) : 0;
    $tmp->payDate = ($tmp->payDate != 0) ? SimpleDate::fromStamp($tmp->payDate) : 0;
    $tmp->cancelledDate = ($tmp->cancelledDate != 0) ? SimpleDate::fromStamp($tmp->cancelledDate) : 0;
    $result->data[] = $tmp;
    $i++;
  }
  echo json_encode($result);
}

function iesumall($myPdo) {
  global $tableTransfer, $tableExpense;
  
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $pageSkip = ($page - 1) * $size;
  
  $createdFrom = MiscUtils::getParam('from', NULL);
  $createdTo = MiscUtils::getParam('to', NULL);
  $userText = MiscUtils::getParam('user', NULL);
  
  $user = json_decode(MiscUtils::getParam('u', NULL));

  $months = _months(json_decode($createdFrom), json_decode($createdTo));
  foreach ($months as &$tmp) {
    $start = SimpleDate::toStamp(SimpleDate::create($tmp->year, $tmp->month, 1));
    $end = SimpleDate::toStamp(SimpleDate::create($tmp->year, $tmp->month, _days($tmp->year, $tmp->month)));
    
    $start = max(array(SimpleDate::toStamp(json_decode($createdFrom)), $start));
    $end = min(array(SimpleDate::toStamp(json_decode($createdTo)), $end));
    
    $condition1 = 'WHERE ' . $condition;
    $condition1 .= ($userText) ? ' AND (d.transferto LIKE ' . $userText . ')' : '';
    $condition1 .= ($start) ? ' AND (d.transferDate >= \'' . $start . '\')' : '';
    $condition1 .= ($end) ? ' AND (d.transferDate <= \'' . $end . '\')' : '';

    $condition2 = 'WHERE ' . $condition;
    $condition2 .= ($userText) ? ' AND (d.userAccount LIKE ' . $userText . ')' : '';
    $condition2 .= ($start) ? ' AND (d.payDate >= \'' . $start . '\')' : '';
    $condition2 .= ($end) ? ' AND (d.payDate <= \'' . $end . '\')' : '';

    if ($user) {
      if ($user->type == USER_TYPE_STAFF) {
        $condition1 = $condition1 . " AND (d.transferto_trackId = '$user->trackId')";
        $condition2 = $condition2 . " AND (d.userTrackId = '$user->trackId')";
      } else {
        $condition1 = $condition1;
        $condition2 = $condition2;
      }
    }
    $sql = "SELECT SUM(d.amount) AS result FROM $tableTransfer AS d $condition1 AND d.status != -1";
    $stmt = $myPdo->prepare($sql);
    $stmt->execute();
    if ($stmt->rowCount() == 1) {
      $tmp2 = $stmt->fetch(PDO::FETCH_OBJ);
      $tmp->receipt = ($tmp2->result) ? $tmp2->result : 0;
    }
    
    $sql = "SELECT SUM(d.amount) AS result FROM $tableExpense AS d $condition2 AND d.status != -1";
    $stmt = $myPdo->prepare($sql);
    $stmt->execute();
    if ($stmt->rowCount() == 1) {
      $tmp2 = $stmt->fetch(PDO::FETCH_OBJ);
      $tmp->expense = ($tmp2->result) ? $tmp2->result : 0;
    }
  }
  echo json_encode($months);
}

function iesumtransferin($myPdo) {
  global $tableTransfer;
    
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.id');
  $queue = MiscUtils::getParam('q', 'DESC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', INFINITE);
  $pageSkip = ($page - 1) * $size;
  
  $createdFrom = MiscUtils::getParam('from', NULL);
  $createdTo = MiscUtils::getParam('to', NULL);
  $userText = MiscUtils::getParam('user', NULL);
  
  $user = json_decode(MiscUtils::getParam('u', NULL));
  
  $months = _months(json_decode($createdFrom), json_decode($createdTo));
  foreach ($months as &$tmp) {
    $start = SimpleDate::toStamp(SimpleDate::create($tmp->year, $tmp->month, 1));
    $end = SimpleDate::toStamp(SimpleDate::create($tmp->year, $tmp->month, _days($tmp->year, $tmp->month)));
    
    $condition1 = 'WHERE ' . $condition;
    
    $start = max(array(SimpleDate::toStamp(json_decode($createdFrom)), $start));
    $end = min(array(SimpleDate::toStamp(json_decode($createdTo)), $end));

    $condition1 = 'WHERE ' . $condition;
    $condition1 .= ($userText) ? ' AND (d.transferto LIKE ' . $userText . ')' : '';
    $condition1 .= ($start) ? ' AND (d.transferDate >= \'' . $start . '\')' : '';
    $condition1 .= ($end) ? ' AND (d.transferDate <= \'' . $end . '\')' : '';

    if ($user) {
      if ($user->type == USER_TYPE_STAFF) {
        $condition1 = $condition1 . " AND (d.transferto_trackId = '$user->trackId')";
      } else {
        $condition1 = $condition1;
      }
    }
    
    $sql = "SELECT SUM(d.amount) AS result FROM $tableTransfer AS d $condition1 AND d.status != -1";
    $stmt = $myPdo->prepare($sql);
    $stmt->execute();
    if ($stmt->rowCount() == 1) {
      $tmp2 = $stmt->fetch(PDO::FETCH_OBJ);
      $tmp->receipt = ($tmp2->result) ? $tmp2->result : 0;
    }
  }
  echo json_encode($months);
}
?>